package net.jwtools.tmt.util;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.sql.DataSource;

import net.jwtools.tmt.dao.hibernate.TerritoryHibernateDAO;
import net.jwtools.tmt.domain.Territory;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dbunit.DatabaseUnitException;
import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ReplacementDataSet;
import org.dbunit.dataset.excel.XlsDataSet;
import org.dbunit.ext.hsqldb.HsqldbDataTypeFactory;
import org.dbunit.operation.DatabaseOperation;
import org.hibernate.Hibernate;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceUtils;

/**
 * Utility for importing and exporting data.
 * 
 * @author Naftali van der Loon
 */

public class ImportExportUtil {

	private static final Log logger = LogFactory.getLog(ImportExportUtil.class);

	private static IDatabaseConnection dbUnitCon;
	private static Connection con;
	private static DataSource ds;
	private static Statement statement;
	private static BeanFactory factory;
	private static TerritoryHibernateDAO dao;

	public static void main(String[] args) {
		ImportExportUtil iou = new ImportExportUtil();
		//iou.initProductionDatabase();
		//iou.loadData(new String[] {
		 //"net/jwtools/tmt/hibernate/initialdatabase.xls" });
		 iou.initTestDatabase();
		 //iou.loadData(new String[] {
		 //"net/jwtools/tmt/hibernate/initialdatabase.xls" });
		//ImportExportUtil.importImages("D:/developer/workspace/TMT/images/");
		//ImportExportUtil.exportImages("D:/developer/workspace/TMT/images/exp/");
	}

	static {
		ClassPathResource resource = new ClassPathResource(
				"net/jwtools/tmt/ctx/tmt-data-access-context.xml");
		factory = new XmlBeanFactory(resource);
		ds = (DataSource) factory.getBean("dataSource");
		dao = (TerritoryHibernateDAO) factory
		.getBean("territoryHibernateDAO");
	}

	private void createConnection(String schema) throws IOException, SQLException {
		con = DataSourceUtils.getConnection(ds);
		statement = con.createStatement();
		dbUnitCon = new DatabaseConnection(con, schema);
		dbUnitCon.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, 
	                        new HsqldbDataTypeFactory());
		
	}

	private void loadScript() throws IOException, SQLException {
		ClassPathResource script = new ClassPathResource(
				"/initialdatabase.sql.ddl");
		BufferedReader reader = new BufferedReader(new FileReader(script
				.getFile()));
		String line;
		StringBuffer query = new StringBuffer();
		boolean queryEnds = false;

		while ((line = reader.readLine()) != null) {
			if (isComment(line))
				continue;
			queryEnds = checkStatementEnds(line);
			query.append(line);
			if (queryEnds) {
				System.out.println("query->" + query);
				statement.addBatch(query.toString());
				query.setLength(0);
			}
		}
	}

	private boolean isComment(String line) {
		if ((line != null) && (line.length() > 0))
			return (line.charAt(0) == '#');
		return false;
	}

	private boolean checkStatementEnds(String s) {
		return (s.indexOf(';') != -1);
	}

	
	private void initSchema(String schema) throws SQLException {
		statement.addBatch("DROP SCHEMA " + schema + ";");
		statement.addBatch("CREATE SCHEMA " + schema + ";");
		statement.addBatch("SET SCHEMA " + schema + ";");
	}

	public void initProductionDatabase() {
		try {
			createConnection(null);
			loadScript();
			statement.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public void initTestDatabase() {
		try {
			createConnection("TEST");
			initSchema("TEST");
			loadScript();
			statement.executeBatch();

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void importImages(String directory) {
		logger.info("Loading data");
		FileInputStream fileInputStream;
		Blob photo;
		List<Territory> trs = dao.findAll();
		for (Territory territory : trs) {
			try {
				System.out.println("Trying to connect image to "
						+ territory.getNumber());
				File image = new File(directory + territory.getNumber()
						+ ".png");
				if (image.exists()) {
					fileInputStream = new FileInputStream(image);
					photo = Hibernate.createBlob(fileInputStream);
					territory.setMap(photo);
					// territory.getArea().setMap(map);
					dao.saveOrUpdate(territory);
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	public static void exportImages(String directory) {
		List<Territory> trs = dao.findAll();
		for (Territory territory : trs) {
			try {
				if (territory.getMap()!=null) {
					InputStream inputStream = territory.getMap().getBinaryStream();
					FileOutputStream fileOutputStream = new FileOutputStream(
							directory + territory.getNumber()
							+ ".png");
					byte[] buf = new byte[1];
					int len = 0;
					while ((len = inputStream.read(buf)) != -1) {
						fileOutputStream.write(buf, 0, len);
					}
					inputStream.close();
					fileOutputStream.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public void loadData(String[] datafiles) {

		try {
			logger.info("Loading data");
			IDataSet[] datasets = getDataSets(datafiles);
			for (int i = 0; i < datasets.length; i++) {
				logger.debug("loading dataset " + datasets[i]);

				DatabaseOperation.CLEAN_INSERT.execute(dbUnitCon, datasets[i]);
			}
		} catch (DatabaseUnitException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DataSourceUtils.releaseConnection(con, ds);
		}
		logger.info("Data succesfully loaded");
	}

	/**
	 * Populates a dataset from classpath resources.
	 * 
	 * @return
	 */
	private IDataSet[] getDataSets(String[] data) {
		IDataSet[] dataSets = new IDataSet[data.length];

		try {
			for (int i = 0; i < dataSets.length; i++) {
				ClassPathResource dset = new ClassPathResource(data[i]);
				logger.debug("Loading " + dset.getFilename());
				ReplacementDataSet dataSet = new ReplacementDataSet(
						new XlsDataSet(dset.getInputStream()));
				dataSet.addReplacementObject("NULL", null);
				dataSets[i] = dataSet;
			}
		} catch (DataSetException e) {
			logger.error("Cannot load dataset!", e);
			e.printStackTrace();
		} catch (IOException e) {
			logger.error("Cannot find dataset!", e);
			e.printStackTrace();
		}
		return dataSets;
	}

}
